package com.management.department.teacherinfosystem.Repository;

import com.management.department.teacherinfosystem.Entity.CourseHours;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface CourseHoursRepository extends JpaRepository<CourseHours, Long>, JpaSpecificationExecutor<CourseHours> {
    
    // 根据创建者查询
    List<CourseHours> findByCreatedBy(Long createdBy);
    
    // 根据学年学期查询
    List<CourseHours> findByAcademicYear(String academicYear);
    
    // 根据课程名称模糊查询
    List<CourseHours> findByCourseNameContaining(String courseName);
    
    // 根据创建者和学年学期查询
    List<CourseHours> findByCreatedByAndAcademicYear(Long createdBy, String academicYear);
    
         // 根据成员教师姓名查询（通过关联查询）
     @Query("SELECT DISTINCT ch FROM CourseHours ch JOIN ch.members m WHERE m.userName = :userName")
     List<CourseHours> findByMemberUserName(@Param("userName") String userName);
     
     // 根据成员教师姓名和学年学期查询
     @Query("SELECT DISTINCT ch FROM CourseHours ch JOIN ch.members m WHERE m.userName = :userName AND ch.academicYear = :academicYear")
     List<CourseHours> findByMemberUserNameAndAcademicYear(@Param("userName") String userName, @Param("academicYear") String academicYear);
    
    // 统计指定学年学期的总标准课时量
    @Query("SELECT SUM(ch.standardHours) FROM CourseHours ch WHERE ch.academicYear = :academicYear")
    Double sumStandardHoursByAcademicYear(@Param("academicYear") String academicYear);
    
    // 统计指定学年学期的总折合课时量
    @Query("SELECT SUM(ch.convertedHours) FROM CourseHours ch WHERE ch.academicYear = :academicYear")
    Double sumConvertedHoursByAcademicYear(@Param("academicYear") String academicYear);
    
    // 统计指定用户的总标准课时量
    @Query("SELECT SUM(ch.standardHours) FROM CourseHours ch WHERE ch.createdBy = :userId")
    Double sumStandardHoursByCreatedBy(@Param("userId") Long userId);
    
    // 统计指定用户的总折合课时量
    @Query("SELECT SUM(ch.convertedHours) FROM CourseHours ch WHERE ch.createdBy = :userId")
    Double sumConvertedHoursByCreatedBy(@Param("userId") Long userId);
}
